package com.hz.ys_stock.dbsql;



import com.hz.framework.MyInterFace.SystemInterFace;

/**
 * *****************************************************************************
 *               Copyright(c) 2010 by 哈尔滨华泽数码科技有限公司.
 *                       All rights reserved.
 *******************************************************************************
 *     File Name         :  com.hz.ys_stock.dbsql.StockSql.java
 *     Description(说明)	:  库存管理 sql 语句
 * -----------------------------------------------------------------------------
 *     No.        Date              Revised by           Description	
 *     	1	  Jun 10, 2011           韩飞              Created
 *****************************************************************************
 */
public class StockSql implements SystemInterFace {
	
//	查询供应商信息
	public static final String SEL_SUPPLIER = "select * from pres_supplier where 1=1";
	
//	根据id查询供应商信息
	public static final String SEL_SUPPLIER_INFO = "select * from pres_supplier where id = ?";
	
//	删除供应商信息
	public static final String DEL_SUPPLIER_INFO = "delete from pres_supplier where id = ?";
	

//	查询材料信息
	public static final String SEL_MAKINGS = "select * from pres_makings where 1=1";
	
//	根据id查询材料信息
	public static final String SEL_MAKINGS_INFO = "select * from pres_makings where gid = ?";
	
//	删除材料信息
	public static final String DEL_MAKINGS_INFO = "delete from pres_makings where gid = ?";
	
	
//	查询采购单信息  经典连接语句 包含","
	public static final String SEL_PURCHASE = "select p.*,r.suppliername,t.finalflag from pres_purchase p " +
//			关联供应商信息
			" left join pres_supplier r on  p.supplierid = r.supplierid " +
			" left join takingsorpay t on t.orderId like concat('%',p.purchaseid,'%') and t.tpflag = 'purchase' " +
			" where 1=1 ";
	
//	根据purchaseid查询采购单信息
	public static final String UPD_PURCHASE_INFO = "select p.*,r.*,personname from pres_purchase p " +
//			关联供应商信息
			" left join pres_supplier r on  p.supplierid = r.supplierid " +
//			关联人员信息
			" left join hzsyspersonnel on p.createuser = personid where purchaseid = ?";
	
//	根据purchaseid查询采购物料信息
	public static final String UPD_MAKINGS_INFO = "select b.*,m.goodsname,m.goodstype,m.brand,m.standard,m.unit,m.price as priceone,m.makingtype  " +
//			关联原材料信息
			" from pres_buymak b left join pres_makings m on b.goodsid = m.goodsid where b.purchaseid = ?";
	
//	删除采购单信息
	public static final String DEL_PURCHASE_INFO = "delete from pres_purchase where purchaseid = ?";
	
//	删除采购单信息
	public static final String DEL_BUYMAK_INFO = "delete from pres_buymak where purchaseid = ?";
	
//	查询人员信息
	public static final String SEL_BUYER = "select personid,personname from hzsyspersonnel where 1=1";
	

	
	
//	根据 goodsid查询 物料库存信息
	public static final String SEL_STOCK_GOODSID = "select * from pres_stock s " +
//	关联原材料信息
			" left join pres_makings  m on s.goodsid = m.goodsid where s.goodsid = ? ";
	
//	查询物料库存信息
	public static final String SEL_STOCK_INFO = "select * from pres_stock s " +
//	关联原材料信息
			" left join pres_makings  m on s.goodsid = m.goodsid where 1 = 1 ";
	
//	删除库存信息
	public static final String DEL_STOCK_INFO = "delete from pres_stock where sid = ?";
	
//	增加删除库存日志信息
	public static final String INS_STOCK_LOG = "insert into pres_stock_log  select * from pres_stock where sid = ?";
//	删除特殊出入库信息
	public static final String DEL_SPECIAL_INFO = "delete from pres_stockspecial where id = ?";
//	查询特殊出入库信息
	public static final String SEL_SPECIAL_INFO = "select * from pres_stockspecial where 1 = 1 ";
	
	
//	查询物料库存信息
	public static final String SEL_STOCK_LOG = "select * from pres_stock_log s " +
//	关联原材料信息
			" left join pres_makings  m on s.goodsid = m.goodsid where 1 = 1 ";
	
}
